package StudentLesson;

import Navigator.student.StudentEnity;
import TeachLesson.CourseWork.CourseWorkEnity;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

public class StudentLessonDao {
    //获取数据库连接对象
    public Connection getCon(){
        Connection con=null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String url="jdbc:mysql://localhost:3306/jwglxt?serverTimezone=UTC&characterEncoding=utf-8";
            con= DriverManager.getConnection(url,"root","root");
        }catch (Exception e){
            e.printStackTrace();
        }
        return con;
    }

    //查询所有选课对象所有实体
    public ArrayList<StudentLessonEnity> getAllStuLes(String studentNo){
        //获取数据库连接
        Connection con=this.getCon();
        try {
            String sql="SELECT tb_studentlesson.courseNo,tb_studentlesson.workerNo,tb_worker.workerName,tb_course.courseName,tb_student.studentNo from tb_course,tb_worker,tb_studentlesson,tb_student WHERE tb_worker.workerNo=tb_studentlesson.workerNo AND tb_course.courseNo=tb_studentlesson.courseNo AND tb_student.studentNo=tb_studentlesson.studentNo AND tb_studentlesson.studentNo=?";
            PreparedStatement ps=con.prepareStatement(sql);
            ps.setString(1,studentNo);
            ResultSet rs=ps.executeQuery();
            ArrayList<StudentLessonEnity> list=new ArrayList<>();
            while (rs.next()){
                StudentLessonEnity studentLessonEnity=new StudentLessonEnity();
                studentLessonEnity.setStudentNo(rs.getString("studentNo"));
                studentLessonEnity.setWorkerNo(rs.getString("workerNo"));
                studentLessonEnity.setCourseNo(rs.getString("courseNo"));
                studentLessonEnity.setCourseName(rs.getString("courseName"));
                studentLessonEnity.setWorkerName(rs.getString("workerName"));
                list.add(studentLessonEnity);
            }
            rs.next();
            ps.close();
            con.close();
            return list;
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }
    }

    //查询选课实体
   /* public ArrayList<CourseWorkEnity> getCourse(String studentNo){
        Connection con=this.getCon();
        try {
            String sql="SELECT tb_coursework.* FROM tb_coursework,tb_studentlesson WHERE tb_coursework.courseNo=tb_studentlesson.courseNo AND tb_studentlesson.workerNo=tb_coursework.workerNo AND studentNo=?";
            PreparedStatement ps=con.prepareStatement(sql);
            ps.setString(1,studentNo);
            ResultSet resultSet=ps.executeQuery();
            while (resultSet.next()){
                CourseWorkEnity courseWorkEnity=new CourseWorkEnity();
                courseWorkEnity.setId(rs.getInt("id"));
                courseWorkEnity.setCourseNo(rs.getString("courseNo"));
                courseWorkEnity.setWorkerNo(rs.getString("workerNo"));
                courseWorkEnity.setEndTime(rs.getString("endTime"));
                courseWorkEnity.setEvaluteType(rs.getString("evaluteType"));
                courseWorkEnity.setTitle(rs.getString("title"));
                courseWorkEnity.setWorkDesc(rs.getString("workDesc"));
                courseWorkEnity.setFilePath(rs.getString("filePath"));
            }
        }catch (Exception e){
            e.printStackTrace();
        }
    }*/

}
